package test.testList;

import org.apache.commons.codec.binary.StringUtils;
import test.bean.TdaTable;
import test.bean.TdaTableColumn;

import java.io.File;
import java.io.FileOutputStream;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

public class ExcelToNewSql {

    public static void createSql(ExcelToTable excelToTable,String filePath, String sqlFileOutputPath,int startRow, boolean isExcel2003) throws Exception{
        Map<String, TdaTable> map = excelToTable.readExcel(filePath, startRow, isExcel2003);

        Collection<TdaTable> tdaTables = map.values();
        Iterator<TdaTable> iterator = tdaTables.iterator();

        while (iterator.hasNext()){
            TdaTable table = iterator.next();
            //表名
            String tableName = table.getTableName();
            String outputFilePath = sqlFileOutputPath + "/" + tableName + ".sql";
            FileOutputStream fileOutputStream = new FileOutputStream(new File(outputFilePath));

            //开头语句
            StringBuilder startSql = new StringBuilder("set define off;\n");
            startSql.append("declare\n")
                    .append("  v_cnt       number(10):=0;\n")
                    .append("  v_cnt_pk    number(10):=0;\n")
                    .append("  v_cnt_index number(10):=0;\n")
                    .append("begin\n");
            //建表语句
            StringBuilder tableSql = new StringBuilder("  select count(*) into v_cnt from user_tables ui where ui.table_name = \'");
            tableSql.append(tableName).append("\';\n")
                    .append("  -- 表存不存在\n")
                    .append("  if v_cnt=0 then\n")
                    .append("    execute immediate \'\n")
                    .append("    create table ").append(tableName).append("\n")
                    .append("    (\n");

            List<TdaTableColumn> tdaTableColumnList = table.getTdaTableColumnList();

            if(tdaTableColumnList != null && tdaTableColumnList.size() > 0) {
                for (int i = 0; i < tdaTableColumnList.size(); i++) {
                    TdaTableColumn tdaTableColumn = tdaTableColumnList.get(i);
                    //字段
                    String columnNumber = tdaTableColumn.getColumnNumber();
                    //类型
                    String dataTypeAndPrecision = tdaTableColumn.getDataTypeAndPrecision();
                    //是否为空
                    String nullAble = tdaTableColumn.getNullAble();
                    //默认值
                    String defaultValue = tdaTableColumn.getDefaultValue();
                    if(defaultValue.endsWith(".0")){
                        defaultValue = defaultValue.substring(0,defaultValue.length()-2);
                    }
                    //字段名称
                    String columnName = tdaTableColumn.getColumnName();

                    tableSql.append("      ").append(columnNumber).append("    ").append(dataTypeAndPrecision);
                    if (!org.springframework.util.StringUtils.isEmpty(defaultValue) && !StringUtils.equals("时间戳（年月日时分秒）", defaultValue) && !StringUtils.equals("uuid", defaultValue) && !StringUtils.equals("序列", defaultValue)) {
                        if (dataTypeAndPrecision.startsWith("NUMBER") || dataTypeAndPrecision.startsWith("INTEGER")){
                            tableSql.append(" default ").append(defaultValue).append(" ");
                        }
                        else{
                            tableSql.append(" default \'\'").append(defaultValue).append("\'\' ");
                        }
                    }
                    if (StringUtils.equals("N", nullAble)) {
                        tableSql.append(" not null");
                    }
                    if (i < tdaTableColumnList.size() - 1) {
                        tableSql.append(",\n");
                    }
                }
            }
            tableSql.append("\n    )");

            //业务主键
            String[] businessPrimaryKeys = table.getBusinessPrimaryKey();
            StringBuilder uniqueSql = new StringBuilder("");
            if(businessPrimaryKeys != null && businessPrimaryKeys.length > 0){
                String businessPrimaryKey = businessPrimaryKeys[0];
                if(businessPrimaryKey != null && businessPrimaryKey != "") {
                    uniqueSql.append("  --索引存不存在\n")
                    //select count(*) into v_cnt_index from user_indexes ui where ui.table_name = 'DGMM_BUSIN_PTY_TYPE_MAP' and ui.index_name =
                            .append("  select count(*) into v_cnt_index from user_indexes ui where ui.table_name = \'").append(tableName).append("\' and ui.index_name = \'uni1_").append(tableName).append("\';\n")
                            .append("  if v_cnt_index = 0 then\n")
                            .append("    execute immediate \'")
                    //create unique index UNI1_DQPC_PRBL_DETAIL on DQPC_PRBL_DETAIL(CHECK_RES_STATS_MAG_ID,PRBL_DETAIL_ID) nologging  local;
                            .append("create unique index uni1_").append(tableName).append(" on ").append(tableName).append("(");
                    for (int i = 0; i < businessPrimaryKeys.length; i++) {
                        uniqueSql.append(businessPrimaryKeys[i]);
                        if (i < businessPrimaryKeys.length - 1) {
                            uniqueSql.append(",");
                        }
                    }
                    if (table.getPartitionField()!=null && table.getPartitionField().length > 0 ){
                        uniqueSql.append(")\';\n");
                    }
                    else{
                        uniqueSql.append(")\';\n");
                    }
                    uniqueSql.append("  end if;\n\n");
                }
            }


            //表主键
            String[] tablePrimaryKeys = table.getTablePrimaryKey();
            StringBuilder primaryKeySql = null;

            //有分区
            if(table.getPartitionField() != null && table.getPartitionField().length > 0){
                String[] partitionFields= table.getPartitionField();
                String partitionField = partitionFields[0];
                tableSql.append("\n    partition by list (");
                for (int i = 0; i < partitionFields.length; i++) {
                    tableSql.append(partitionFields[i]);
                    if (i < partitionFields.length - 1) {
                        tableSql.append(",");
                    }
                }
                tableSql.append(")\n")
                        .append("    (\n")
                        .append("      partition PART_DEFAULT values (\'\'0\'\')")
                        .append("\n    )\';");
                if(tablePrimaryKeys !=null && tablePrimaryKeys.length > 0){
                    String tablePrimaryKey = tablePrimaryKeys[0];
                    if(tablePrimaryKey != null && tablePrimaryKey != "") {
                        uniqueSql.append("  select count(*) into v_cnt_index from user_indexes ui where ui.table_name = \'").append(tableName).append("\' and ui.index_name = \'uni2_").append(tableName).append("\';\n")
                                .append("  if v_cnt_index = 0 then\n")
                                .append("    execute immediate \'")
                                .append("create unique index uni2_")
                                .append(tableName).append(" on ").append(tableName).append("(").append(partitionField).append(",");
                        for (int i = 0; i < tablePrimaryKeys.length; i++) {
                            uniqueSql.append(tablePrimaryKeys[i]);
                            if (i < tablePrimaryKeys.length - 1) {
                                uniqueSql.append(",");
                            }
                        }
                        uniqueSql.append(")\';\n")
                                .append("  end if;\n\n");
                    }
                }
            }else {
                tableSql.append("\';");
                if(tablePrimaryKeys !=null && tablePrimaryKeys.length > 0){
                    String tablePrimaryKey = tablePrimaryKeys[0];
                    if(tablePrimaryKey != null && tablePrimaryKey != "") {
                        //-- 主键存不存在
                        primaryKeySql = new StringBuilder("  -- 主键存不存在\n");
                        primaryKeySql.append("  select count(*) into v_cnt_pk from user_constraints ui where ui.table_name = \'").append(tableName).append("\' and ui.constraint_name = \'pk_").append(tableName).append("\';\n")
                                .append("  if v_cnt_pk = 0 then\n")
                                //execute immediate 'alter table DGMM_BUSIN_PTY_TYPE_MAP add constraint PK_DGMM_BUSIN_PTY_TYPE_MAP primary key (MAP_ID)';
                                .append("    execute immediate \'")
                                .append("alter table ");
                        //alter table 表名 add constraint 主键名 primary key(字段名1,字段名2……)
                        primaryKeySql.append(tableName).append(" add constraint ").append("pk_").append(tableName).append(" primary key(");
                        for (int i = 0; i < tablePrimaryKeys.length; i++) {
                            primaryKeySql.append(tablePrimaryKeys[i]);
                            if (i < tablePrimaryKeys.length - 1) {
                                primaryKeySql.append(",");
                            }
                        }
                        primaryKeySql.append(")\';\n")
                                .append("  end if;\n");
                    }
                }
            }
            tableSql.append("\n  end if;\n");

            StringBuilder lastSql = new StringBuilder("end;\n");
            lastSql.append("/ \n")
                    .append("set define on;\n");

            //换行
            String newLine = System.getProperty("line.separator");

            fileOutputStream.write(startSql.toString().getBytes());
            fileOutputStream.write(newLine.getBytes());

            fileOutputStream.write(tableSql.toString().getBytes());
            fileOutputStream.write(newLine.getBytes());

            if(primaryKeySql != null) {
                fileOutputStream.write(primaryKeySql.toString().getBytes());
                fileOutputStream.write(newLine.getBytes());
            }
            if(uniqueSql != null){
                fileOutputStream.write(uniqueSql.toString().getBytes());
                fileOutputStream.write(newLine.getBytes());
            }
            fileOutputStream.write(lastSql.toString().getBytes());

            //关流
            fileOutputStream.close();
        }
    }

}
